package qiurank;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * Created by Administrator on 2016/1/6.
 */
public class DB extends Object{
    Connection _connection=null;
    PreparedStatement _statement=null;
    private static DB dbobject=null;
    private String _table;
    public static Table table(String tableName){
        if(dbobject==null){
            dbobject=new DB();
        }
        return dbobject.setTable(tableName);
    }
    public static DB createDB(){
        if(dbobject==null){
            dbobject=new DB();
        }
        return dbobject;
    }
    public static String DBName(){
        return Config.getValue("db_name");
    }
    /**
     * 设置表名
     * @param tableName 表名称
     */
    public Table setTable(String tableName){
        return new Table(tableName,this);
    }
    public DB(){
        String url="jdbc:mysql://localhost:3306/"+Config.getValue("db_name")+"?characterEncoding=UTF-8";
        try {
            Class.forName("com.mysql.jdbc.Driver");
            if(_connection!=null){
                _connection.close();
            }
            _connection= DriverManager.getConnection(url,Config.getValue("db_user_name"),Config.getValue("db_user_pass"));
        } catch (Exception e) {
            Log.show("连接数据库异常:"+e.toString());
        }
    }
    public DB(String db_name){
        String url="jdbc:mysql://localhost:3306/"+db_name+"?characterEncoding=UTF-8";
        try {
            Class.forName("com.mysql.jdbc.Driver");
            if(_connection!=null){
                _connection.close();
            }
            _connection= DriverManager.getConnection(url,Config.getValue("db_user_name"),Config.getValue("db_user_pass"));
        } catch (Exception e) {
            Log.show("连接数据库异常:"+e.toString());
        }
    }
    /**
     * 连接数据库
     * @param db_name  数据库名称
     * @return  返回db对象
     */
    public static DB connection(String db_name){
        dbobject=new DB(db_name);
        return dbobject;
    }
    /**
     * 事务开始
     * @throws SQLException
     */
    public void beginTransaction() throws SQLException {
        this.execute("BEGIN");
    }

    /**
     * 事务回滚
     * @throws SQLException
     */
    public void rollBack() {
        try {
            this.execute("ROLLBACK");
        } catch (SQLException e) {
            Log.show("执行事务ROLLBACK失败");
        }
    }

    /**
     * 事务提交
     * @throws SQLException
     */
    public void commit() throws SQLException {
        this.execute("COMMIT");
    }
    /**
     * 执行查询时调用
     * @param sql  查询的sql
     * @return 返回查询结果
     */
    public ResultSet query(String sql) throws SQLException {
        ResultSet resultSet=null;
        if(_connection!=null) {
            _statement = _connection.prepareStatement(sql);
            resultSet = _statement.executeQuery();
        }else {
            Log.show("连接数据库异常,连接失败");
        }

        return resultSet;
    }

    /**
     * 执行增改删时调用，不反回任何结果
     * @param sql  要执行的sql语句
     */
    public void execute(String sql) throws SQLException {
        if(_connection!=null) {
            _statement = _connection.prepareStatement(sql);
            _statement.executeUpdate();
        }else{
            Log.show("连接数据库异常,连接失败");
        }

    }
    @Override
    protected void finalize() throws Throwable {
        if(_connection!=null){
            _connection.close();
        }
    }

    /**
     * 执行mysql事务
     * @param _function  事务函数
     */
    public static void transaction(Function _function){
        if(dbobject==null){
            dbobject=new DB();
        }
        try {
            dbobject.beginTransaction();
            _function.transaction();
            dbobject.commit();
        } catch (SQLException e) {
            Log.show(e.toString());
            dbobject.rollBack();
        }
    }
    public interface Query {
        public void where(Table query)throws SQLException;
    }
    public interface Join{
        public void on(Table query)throws SQLException;
    }
    public class Table {
        private DB dbObject=null;
        private String _table;
        private String _firsttable="";
        private String _where="";
        private String _groupby="";
        private String _orderby="";
        private String _select_sql="";
        private String _childrenwhere="";
        private String _childrensql="";
        private String _childrentable="";
        private String _existsql_prefix="";
        private String _existsql_afterfix="";
        private String _join="";
        private String _on="";
        private String _having="";
        private String _sql="";
        private String _unionsql="";
        private int _skip=0;
        private int _take=0;
        boolean _ison=false;
        private String _table_fix="";
        public Table(String tableName,DB _dbObject){
            _table_fix=Config.getValue("db_table_fix");
            _table=_table_fix+"_"+tableName;
            _where="";
            _groupby="";
            _orderby="";
            _select_sql="";
            _childrensql="";
            _childrentable="";
            _sql="";
            _unionsql="";
            _join="";
            _childrenwhere="";
            _existsql_prefix="";
            _existsql_afterfix="";
            _having="";
            _skip=0;
            _take=0;
            _on="";
            _firsttable=_table;
            _ison=false;
            dbObject=_dbObject;
        }
        public String getTableFix(){
            return _table_fix;
        }
        public String getTableName(){
            return _table;
        }

        /**
         * 插入操作
         * @param data  要插入的数据
         * @throws SQLException  执行插入时候产生的异常
         */
        public void insert(Map<String, Object> data) throws SQLException {
            if (!_table.isEmpty()) {
                StringBuffer sql =new StringBuffer("insert into "+_table);
                StringBuffer key=new StringBuffer("(");
                StringBuffer val=new StringBuffer("values(");
                int i=0;
                for (Map.Entry<String, Object> entry : data.entrySet()) {
                    Object _vl=entry.getValue();
                    if(_vl.getClass().getName().equals(String.class.getName())){
                        _vl="'"+_vl+"'";
                    }
                    if(i==data.size()-1){
                        key.append(entry.getKey());
                        val.append(_vl);
                    }else {
                        key.append(entry.getKey() + ",");
                        val.append(_vl + ",");
                    }
                    i++;
                }
                key.append(")");
                val.append(")");
                sql.append(key+" "+val);
                dbobject.execute(sql.toString());
            } else {
                Log.show("表名为空");
                throw new SQLException("缺少表名");

            }
            clear();
        }

        /**
         * 插入数据并且返回id
         * @param data  待插入的数据
         * @return  返回插入的id
         * @throws SQLException
         */
        public long insertGetId(Map<String, Object> data) throws SQLException {
            insert(data);
            ResultSet rs=dbobject.query("SELECT LAST_INSERT_ID() as id");
            if(rs!=null) {
                rs.next();
                long id=rs.getLong("id");
                rs.close();
                return id;
            }else {
                Log.show("查询结果集为空,查询失败."+this.getClass().getName()+",Method:insertGetId");
                return -1;
            }

        }

        /**
         * 清空表
         * @throws SQLException
         */
        public void truncate() throws SQLException {
            if (!_table.isEmpty()) {
                String sql="TRUNCATE table "+_table;
                dbobject.execute(sql);
            }else {
                throw new SQLException("缺少表名");
            }
        }

        /**
         * 删除操作
         * @throws SQLException
         */
        public void delete() throws SQLException {
            if (!_table.isEmpty()) {
                String sql="delete from "+_table+" "+_where;
                //Log.console(sql);
                dbobject.execute(sql);
            }else {
                throw new SQLException("缺少表名");
            }
        }

        /**
         * 查询操作
         * @param data 要查询的字段名称
         * @return  返回table对象
         * @throws SQLException
         */
        public Table select(String data) throws SQLException {
            if (!_table.isEmpty()) {
                String data_str="";
                String[] key=data.split(",");
                for(int i=0;i<key.length;i++){
                    if(key[i].contains(".")){
                        key[i]=_table_fix+"_"+key[i];
                    }
                    if(i==key.length-1){
                        data_str+=key[i];
                    }else{
                        data_str+=key[i]+",";
                    }
                }

                _select_sql = "select " + data_str + " from ";
            }else{
                throw new SQLException("缺少表名");
            }
            return this;
        }

        /**
         * 查询条件
         * @param key  字段名
         * @param opt  符号（大于，等于，小于）
         * @param val  值
         * @return 返回table对象
         */
        private Table _setwhereraw(String whe,String cond)throws SQLException{
            if(!_table.isEmpty()){
                if(!_where.isEmpty()){
                    _where+=" "+cond+" "+whe;
                }else {
                    _where+=" where "+whe;
                }
            }else{
                throw new SQLException("缺少表名");
            }
            return this;
        }
        private Table _setwhere(String key,String opt,Object val,String cond) throws SQLException {
            if(val.getClass().getName().equals(String.class.getName())){
                val="'"+val+"'";
            }
            if(key.contains(".")){
                key=_table_fix+"_"+key;
            }
            return this._setwhereraw(key + " " + opt + " " + val, cond);
        }
        public Table where(String key,String opt,Object val) throws SQLException {
            return this._setwhere(key, opt, val, "and");
        }
        public Table where(String key,Object val) throws SQLException {
            this.where(key, "=", val);
            return this;
        }
        public Table where(String cond)throws SQLException {
            return this._setwhereraw(cond,"and");
        }
        public Table where(Query query)throws SQLException {
            this._setwhereraw("(1=1","and");
            query.where(this);
            this._setwhereraw(")", "");
            return this;
        }
        private Table _setwhereeists(Query query,String cond,String not)throws SQLException{
            _childrensql=_select_sql;
            _childrentable=_table;
            _existsql_prefix=cond+" "+not+" EXISTS (";
            _childrenwhere=_where;
            _where="";
            _table="";
            query.where(this);
            String tmp="";
            tmp=_childrensql;
            _childrensql=_select_sql;
            _select_sql=tmp;

            tmp=_childrenwhere;
            _childrenwhere=_where;
            _where=tmp;

            tmp=_childrentable;
            if(_table.isEmpty()){
                _childrentable=_firsttable;
            }else {
                _childrentable = _table;
            }
            _table=tmp;
            _existsql_afterfix+=")";
            return this;
        }
        public Table whereExists(Query query)throws SQLException {
            return this._setwhereeists(query,"and","");
        }
        public Table orWhereExists(Query query)throws SQLException {
            return this._setwhereeists(query, "or", "");
        }
        public Table whereNotExists(Query query)throws SQLException {
            return this._setwhereeists(query, "and", "not");
        }
        public Table orWhereNotExists(Query query)throws SQLException {
            return this._setwhereeists(query, "or", "not");
        }
        /**
         * 查询 or
         * @param key  字段名
         * @param opt  符号（大于，等于，小于）
         * @param val  值
         * @return
         * @throws SQLException
         */
        public Table orWhere(String key,String opt,Object val) throws SQLException {
            return this._setwhere(key, opt, val, "or");
        }
        public Table orWhere(Query query)throws SQLException {
            this._setwhereraw("(1=1","or");
            query.where(this);
            this._setwhereraw(")","");
            return this;
        }
        public Table orWhere(String cond)throws SQLException {
            return this._setwhereraw(cond, "or");
        }
        public Table orWhere(String key,Object val) throws SQLException {
            return this.orWhere(key, "=", val);
        }
        private Table _setwhereBetween(String key,Object val1,Object val2,String cond,String not) throws SQLException {
            if(val1.getClass().getName().equals(String.class.getName())){
                val1="'"+val1+"'";
            }
            if(val2.getClass().getName().equals(String.class.getName())){
                val2="'"+val2+"'";
            }
            if(key.contains(".")){
                key=_table_fix+"_"+key;
            }
            return this._setwhereraw(key + " " + not + " BETWEEN " + val1 + " and " + val2, cond);
        }

        /**
         * 查询between 以and连接
         * @param key 字段名
         * @param val1  值1
         * @param val2  值2
         * @return
         * @throws SQLException
         */
        public Table whereBetween(String key,Object val1,Object val2) throws SQLException {
            return this._setwhereBetween(key, val1, val2, "and", "");
        }

        /**
         * 查询between 以or连接
         * @param key 字段名
         * @param val1 值1
         * @param val2 值2
         * @return
         * @throws SQLException
         */
        public Table orWhereBetween(String key,Object val1,Object val2) throws SQLException {
            return this._setwhereBetween(key, val1, val2, "or", "");
        }
        public Table whereNotBetween(String key,Object val1,Object val2) throws SQLException {
            return this._setwhereBetween(key, val1, val2, "and", "NOT");
        }
        public Table orWhereNotBetween(String key,Object val1,Object val2) throws SQLException {
            return this._setwhereBetween(key, val1, val2, "or", "NOT");
        }

        private Table _setwherein(String key,List<?> val,String cond,String not) throws SQLException {
            String val_str="(";
            int i=0;
            for (Object obj:val){
                if(obj.getClass().getName().equals(String.class.getName())){
                    obj="'"+obj+"'";
                }
                if(i<val.size()-1){
                    val_str+=obj+",";
                }else {
                    val_str+=obj;
                }
                i++;
            }
            val_str+=")";
            if(key.contains(".")){
                key=_table_fix+"_"+key;
            }
            return this._setwhereraw(key + " " + not + " in" + val_str, cond);

        }

        public Table whereIn(String key,List<?> val) throws SQLException {
            return this._setwherein(key, val, "and", "");
        }

        public Table orWhereIn(String key,List<?> val) throws SQLException {
            return this._setwherein(key, val, "or", "");
        }
        public Table whereNotIn(String key,List<?> val) throws SQLException {
            return this._setwherein(key, val, "and", "not");
        }
        public Table orWhereNotIn(String key,List<?> val) throws SQLException {
            return this._setwherein(key, val, "or", "not");
        }



        private Table _setwherenull(String key,String cond,String not)throws SQLException{
            if(key.contains(".")){
                key=_table_fix+"_"+key;
            }
            return this._setwhereraw(key + " IS " + not + " NULL", cond);
        }
        //查询值为空的
        public Table whereNull(String key)throws SQLException{
            return this._setwherenull(key, "and", "");
        }
        public Table orWhereNull(String key)throws SQLException{
            return this._setwherenull(key, "or", "");
        }
        public Table whereNotNull(String key)throws SQLException{
            return this._setwherenull(key, "and", "NOT");
        }
        public Table orWhereNotNull(String key)throws SQLException{
            return this._setwherenull(key, "or", "NOT");
        }
        /**
         * 查询分组
         * @param gby  要分组的字段名
         * @return
         */
        public Table groupBy(String gby){
            _groupby="group by "+gby;
            return this;
        }

        /**
         * having 查询分组条件
         * @param hav 查询分组条件
         * @return
         */
        private Table _setrawhaving(String hav,String cond) throws SQLException {
            if(!_table.isEmpty()) {
                if (!_having.isEmpty()) {
                    _having += " " + cond + " " + hav;
                } else {
                    _having += " having " + hav;
                }
            }else {
                throw new SQLException("缺少表名");
            }
            return this;
        }
        private Table _sethaving(String key,String opt,Object val,String cond) throws SQLException {
            if(val.getClass().getName().equals(String.class.getName())){
                val="'"+val+"'";
            }
            if(key.contains(".")){
                key=_table_fix+"_"+key;
            }
            return this._setrawhaving(key + opt + val, cond);

        }
        public Table having(String key,String opt,Object val) throws SQLException {
            return this._sethaving(key, opt, val, "and");
        }

        public Table having(String key,Object val) throws SQLException {
            return this.having(key, "=", val);
        }

        /**
         * 使用原始的having查询
         * @param hav 查询having语句 and连接
         * @return
         * @throws SQLException
         */
        public Table havingRaw(String hav) throws SQLException {
            return this._setrawhaving(hav, "and");
        }

        /**
         * 使用原始的having查询
         * @param hav 查询having语句 or连接
         * @return
         * @throws SQLException
         */
        public Table orHavingRaw(String hav) throws SQLException {
            return this._setrawhaving(hav, "or");
        }

        /**
         * 分组条件或
         * @param key 字段名
         * @param opt 符号（大于，等于，小于）
         * @param val 值
         * @return
         * @throws SQLException
         */
        public  Table orHaving(String key,String opt,Object val) throws SQLException {
            return this._sethaving(key, opt, val, "or");
        }
        public  Table orHaving(String key,Object val) throws SQLException {
            return this.orHaving(key, "=", val);
        }

        /**
         * 排序
         * @param key  字段名
         * @param sort asc或者desc
         * @return
         */
        public Table orderBy(String key,String sort){
            if(key.contains(".")){
                key=_table_fix+"_"+key;
            }
            if(_orderby.isEmpty()) {
                _orderby = "order by " + key + " " + sort;
            }else{
                _orderby +=","+key+" "+sort;
            }
            return this;
        }

        /**
         * limit 的第一个值，表示要跳过多少条记录，从哪开始读取
         * @param skp  跳个的记录数
         * @return
         */
        public Table skip(int skp){
            _skip=skp;
            return this;
        }

        /**
         * limit 的第二个值，表示要读取多少条记录，读取记录数
         * @param tke 读取记录数
         * @return
         */
        public Table take(int tke){
            _take=tke;
            return this;
        }
        private String getLimit(){
            if(_take>0&&_skip>0){
                return "limit "+_skip+","+_take;
            }else if(_take>0){
                return "limit 0,"+_take;
            }
            return "";
        }
        private Table _setjoin(String tablename,String key1,String opt,String key2,String jointype) throws SQLException {
            _join="";
            _on="";
            if(!_table.isEmpty()) {
                _join = " " + jointype + " " + _table_fix + "_" + tablename;
                if(_on.isEmpty()){
                    _on=" on " + _table_fix + "_" + key1 + opt + _table_fix + "_" + key2;
                }
                _table+=_join+_on;
            }else {
                throw new SQLException("缺少表名");
            }
            return this;
        }
        public void on(String key1,String opt,String key2) throws SQLException {
            if(_ison) {
                if(_on.isEmpty()){
                    _on=" on " + _table_fix  + "_" + key1 + opt + _table_fix + "_" + key2;
                }else{
                    _on+=" and " + _table_fix + "_" + key1 + opt + _table_fix  + "_" + key2;
                }
            }else{
                throw new SQLException("无权限调用on函数");
            }
        }
        public void orOn(String key1,String opt,String key2) throws SQLException {
            if(_ison) {
                if(_on.isEmpty()){
                    _on=" on " + _table_fix + "_" + key1 + opt + _table_fix + "_" + key2;
                }else{
                    _on+=" or " + _table_fix  + "_" + key1 + opt + _table_fix  + "_" + key2;
                }
            }else{
                throw new SQLException("无权限调用orOn函数");
            }
        }

        /**
         * join查询
         * @param tablename 要连接的表名称
         * @param key1       表1字段名称
         * @param opt        符号
         * @param key2       表2字段名称
         * @return
         * @throws SQLException
         */
        public Table join(String tablename,String key1,String opt,String key2) throws SQLException {
            return this._setjoin(tablename,key1,opt,key2,"join");
        }
        private Table __setjoin(String tablename,Join join,String jointype) throws SQLException {
            _join="";
            _on="";
            _ison=true;
            _join = " " + jointype + " " + _table_fix  + "_" + tablename;
            join.on(this);
            _table+=_join+_on;
            _ison=false;
            return this;
        }
        public Table join(String tablename,Join join) throws SQLException{
            return this.__setjoin(tablename, join, "join");
        }
        public Table leftJoin(String tablename,String key1,String opt,String key2) throws SQLException {
            return this._setjoin(tablename, key1, opt, key2, "LEFT JOIN");
        }
        public Table leftJoin(String tablename,Join join) throws SQLException{
            return this.__setjoin(tablename, join, "LEFT JOIN");
        }
        public Table rightJoin(String tablename,String key1,String opt,String key2) throws SQLException {
            return this._setjoin(tablename, key1, opt, key2, "RIGHT JOIN");
        }
        public Table rightJoin(String tablename,Join join) throws SQLException{
            return this.__setjoin(tablename, join, "RIGHT JOIN");
        }
        public String getSql(){
            return _sql;
        }
        public Table union(Table t1){
            t1.genSql();
            _unionsql+="UNION "+t1.getSql();
            return this;
        }
        private void clear(){
            _where="";
            _groupby="";
            _orderby="";
            _select_sql="";
            _childrensql="";
            _childrentable="";
            _sql="";
            _unionsql="";
            _join="";
            _childrenwhere="";
            _existsql_prefix="";
            _existsql_afterfix="";
            _having="";
            _skip=0;
            _take=0;
            _on="";
            _ison=false;
        }

        /**
         * 生成sql语句
         */
        private void genSql(){
            if(!_childrensql.isEmpty()&&_childrenwhere.isEmpty()){
                _sql=(_select_sql+_table + " " + _existsql_prefix + _childrensql+_table+ " " + _existsql_afterfix + _where + " " + _groupby + " " + _having + " " + _orderby + " " + getLimit())+" "+_unionsql;
            }else if(!_childrensql.isEmpty()&&!_childrenwhere.isEmpty()){
                _sql=(_select_sql+_table+ _where+" "+_existsql_prefix+_childrensql+_table +" "+_childrenwhere+_existsql_afterfix+ " " + _groupby + " " + _having + " " + _orderby + " " + getLimit())+" "+_unionsql;
            }else {
                if(_select_sql.isEmpty()){
                    _sql = ("select * from " + _table + _where + " " + _groupby + " " + _having + " " + _orderby + " " + getLimit()) + " " + _unionsql;
                }else {
                    _sql = (_select_sql + _table + _where + " " + _groupby + " " + _having + " " + _orderby + " " + getLimit()) + " " + _unionsql;
                }
            }

        }

        /**
         * 执行查询语句
         * @return 返回查询结果集
         */
        public ResultSet get(){
            genSql();
            try {

                //Log.console(_sql);
                ResultSet rs =dbObject.query(_sql);
                clear();
                return rs;
            } catch (SQLException e) {
                clear();
                return null;
            }

        }

        /**
         * 更新操作
         * @param data 要更新的数据
         * @return 成功返回true 否则返回false
         */
        public boolean update(Map<String, Object> data){
            StringBuffer sql =new StringBuffer("update "+_table) ;
            StringBuffer set=new StringBuffer(" set ");
            int i=0;
            for (Map.Entry<String, Object> entry : data.entrySet()) {
                Object _vl=entry.getValue();
                if(_vl.getClass().getName().equals(String.class.getName())){
                    _vl="'"+_vl+"'";
                }
                if(i==data.size()-1){
                    set.append(entry.getKey()+"="+_vl);
                }else {
                    set.append(entry.getKey()+"="+_vl+",");
                }
                i++;
            }
            sql.append(set.toString()+_where);
            try {
                dbobject.execute(sql.toString());
                clear();
                return true;
            } catch (SQLException e) {
                //e.printStackTrace();
            }
            return false;
        }
    }
}
